 <!DOCTYPE html>
 
<%@ page import="java.sql.*" %> 
<%@ page import="java.io.*" %> 
<%@ page import="java.util.Date" %> 
<%@ page import="java.text.DateFormat" %> 
<%@ page import="java.text.SimpleDateFormat" %> 


<% 
try {
	
	String connectionURL = "jdbc:mysql://localhost:3306/unilearn"; 
	
	int c_row_count = 0;
	Connection connection = null;
	Statement stmt = null;
	ResultSet rst=null;

	Class.forName("com.mysql.jdbc.Driver").newInstance(); 

	connection = DriverManager.getConnection(connectionURL, "root", "");
	stmt = connection.createStatement();
	
	if(!connection.isClosed())
	{
		
		
		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		DateFormat dateFormat2 = new SimpleDateFormat("yyyy-MM-dd");
		Date _now = new Date();
		
		
		//String full_s_date = "";
		//String full_e_date = "";
		//if (s_dd != "" && s_mm != "" && s_yy != "")
		//{
		//	 full_s_date = s_yy + "-" + s_mm + "-" + s_dd + " 00:00:00";
		//}
		
		//if (e_dd != "" && e_mm != "" && e_yy != "")
		//{
		//	full_e_date = e_yy + "-" + e_mm + "-" + e_dd + " 00:00:00";
		//}
		
		//get session values
		String user_id = (String)session.getAttribute("UserID");
		String permission = (String)session.getAttribute("Permission");
		String first_name = (String)session.getAttribute("LastName");
		String last_name = (String)session.getAttribute("FirstName");
		String class_group_id = (String)session.getAttribute("ClassGroupID");
	
%>

<html lang="en">
<head>
<script type="text/javascript">
	
</script>
<link rel="stylesheet" type="text/css" href="common.css">
<title>UniLearn System</title>
</head>
<body>
<form action="assignment_submit_state.jsp" method="post" name="entry" onSubmit="return validate(this)">
<table class="TblBody" style="width:720px">
	<tr>
		<td>
			<div class="contentb">
				<table width="100%">
                    <tr>
						<%
							String subject_id = request.getParameter("subject_id");
							String subject_code = "";
							if (subject_id != null && subject_id != "")
							{
								ResultSet rs = null;
								
								rs = stmt.executeQuery("select * from subject where subject_id = '"+ subject_id +"'");
								
								while(rs.next()){
									if(rs.getString("subject_code") != null && rs.getString("subject_code") != ""){
										subject_code = rs.getString("subject_code");
										session.setAttribute( "SubjectCode",  rs.getString("subject_code"));
									}
								}
							}
						%>
                        <td style="background-color:#ccff99; font-size:20px; font-weight:bold; height:30px">Submit Assignment Status <%= subject_code %></td>
						
                    </tr>
					<tr>
                        <td>
							<table class="SubTable">
								<tr>
									<th>Assignment</th>
									<th>Status</th>
									<th>Due Date</th>
									<th>Action</th>
								</tr>
							<%
								//rst = stmt.executeQuery("select * from assignment where class_group_id = '"+ class_group_id +"'" +
								//						"and subject_id = '"+ subject_id +"'" + 
								//						"and (select count(*) from subject_mapping where user_id = '"+user_id+"' and subject_id = '"+ subject_id +"') > 0");
								
								rst = stmt.executeQuery("select *, " +
														"(select count(*) from assignment_submission where assignment_id = assignment.assignment_id " +
														"and (user_group_id = (select user_group_id from group_tbl where user_id = '"+user_id+"' and subject_id = '"+subject_id+"') or commit_user = '1')) as assignment_count, " +
														"(select assignment_sub_id from assignment_submission where assignment_id = assignment.assignment_id " +
														"and (user_group_id = (select user_group_id from group_tbl where user_id = '"+user_id+"' and subject_id = '"+subject_id+"') or commit_user = '1')) as assignment_sub_id " +
														"from assignment where subject_id = '"+subject_id+"' " +
														"and (select count(*) from subject_mapping where user_id = '"+user_id+"' and subject_id = '"+subject_id+"' ) > 0 ");
														//class_group_id = '"+class_group_id+"' and
								
								
								while(rst.next())
								{
								%>
									<tr>
										<td><a><%= rst.getString("assignment_name")%></a></td>
										<td>
											<%
												String ass_count = rst.getString("assignment_count");
												String ass_sub_id = rst.getString("assignment_sub_id");
												String ass_name = rst.getString("assignment_name");
												int _ass_count = 0;
												if(ass_count.length() > 0){_ass_count = Integer.parseInt(ass_count);}
												
												if(_ass_count > 0){
											%>
												Submited
											<%
												}else{
											%>
												Not Submited
											<%
												}
											%>
										</td>
										<td><a><%= dateFormat2.format(dateFormat2.parse(rst.getString("end_date").toString()))%></a></td>
										<td>
											<%if(_ass_count > 0){%>
												<a href="assignmentMarkDetail.jsp?assignmentSubID=<%= ass_sub_id %>&assignmentName=<%= ass_name %>">View</a>
											<%}else{%>
												<a href="index_single_upload.jsp?assignment_id=<%=rst.getString("assignment_id")%>&assignment_type=<%=rst.getString("assignment_type")%>">View</a>
											<%}%>
										</td>
										
									</tr>
								<%
								}
							%>
							</table>
                        </td>
					</tr>
				</table>
			</div>
		</td>
	</tr>
</table>
</form>
<%
}
	}catch(Exception ex){

		out.println(ex);
	}
%>
</body>
</html>